import { QueryTypes } from "sequelize";
import { sequelize } from "../lib/database.js";
import logger from "../lib/log4js.js";
import { RuFormMapper } from "./ru-form.ts";
import { USER_TYPES } from "../entity/common-defines.ts";
import { XlsxData } from "./xlsx-data.ts";
import { EvaProjectMapper } from "./eva-project.ts";
class ResultDemoEvaMapper extends XlsxData {


    private formMapper: RuFormMapper = new RuFormMapper;
    private projectMapper: EvaProjectMapper = new EvaProjectMapper;
    public async getResultData(project_id: string, test: boolean): Promise<SheetData> {
        const form: any = await this.formMapper.findOneByQuery({ where: { user_type: USER_TYPES.DEMO_EVA } })
        const formId: string = form?.id;
        if (!formId) throw new Error("对应测评表不存在")
        const sql = `
             select 
  rule_name, 
  sum(total) amount,
  ifnull(max(CASE WHEN vote_value = '好' THEN ifnull(total,0) end),0)  AS a,
  ifnull(max(CASE WHEN vote_value = '一般' THEN ifnull(total,0) end),0)  AS b,
  ifnull(max(CASE WHEN vote_value = '不好' THEN ifnull(total,0) end),0)  AS c,
  ifnull(max(CASE WHEN vote_value = '不了解' THEN ifnull(total,0) end),0)  AS d,
  100*(ifnull(max(CASE WHEN vote_value = '好' THEN ifnull(total,0) end),0)+ifnull(max(CASE WHEN vote_value = '一般' THEN ifnull(total,0) end),0))/sum(total) posiv_pert 
from (        
        select				
			ea.rule_name,			
			json_value(ifnull(ea.rule_value, '[0]'), '$[0]') vote_value,
			count(*) total
		from
			eva_answer ea
		inner join eva_project_owner epo on
			epo.project_id = ea.project_id
			and epo.id = ea.project_owner_id		
		where
            ea.is_test is ?
			and ea.project_id = ?
			and ea.user_type = ?
			and ea.rule_form_id = ?
			and ea.rule_name in ('对本单位选人用人工作的总体评价','对本单位从严管理监督干部情况的看法')
		group by rule_name,vote_value	
) dmv 
group by dmv.rule_name;
        `;
        const sql2 = `
          -- 单位民主评议-2
select
 rule_name,
 ifnull(sum(CASE WHEN item = '①落实党中央关于领导班子和干部队伍建设工作要求有差距' THEN ifnull(sub_total,0) end) ,0) as a,
 ifnull(sum(CASE WHEN item = '②选人用人把关不严，质量不高' then  ifnull(sub_total,0) end )  ,0) as b,
 ifnull(sum(CASE WHEN item = '③坚持事业为上不够，不能做到以事择人，人岗相适' THEN ifnull(sub_total,0) end)  ,0) as c,
 ifnull(sum(CASE WHEN item = '④激励担当作为用人导向不明，论资排辈情况比较严重' THEN ifnull(sub_total,0) end)  ,0) as d,
 ifnull(sum(CASE WHEN item = '⑤选人用人"个人说了算"' THEN ifnull(sub_total,0) end)  ,0) as e,
 ifnull(sum(CASE WHEN item = '⑥任人唯亲，拉帮结派' THEN ifnull(sub_total,0) end),0) as f,
 ifnull(sum(CASE WHEN item = '⑦跑官要官，买官卖官，说情打招呼' THEN ifnull(sub_total,0) end)  ,0) as g,
 ifnull(sum(CASE WHEN item = '⑧执行干部选拔任用政策规定不严格' THEN ifnull(sub_total,0) end)  ,0) as h,
 ifnull(sum(CASE WHEN item = '⑨干部队伍建设统筹谋划不够，结构不合理' then ifnull(sub_total,0) end)  ,0) as i,
 ifnull(sum(CASE WHEN item = '⑩干部队伍能力素质不适合工作要求' then ifnull(sub_total,0) end)   ,0) as  j, 
 sum(sub_total) all_total
from (
        select	
            ea.rule_name ,
			vt.item,
			ifnull(count(*),0) sub_total			
		from
			eva_answer ea
		left join JSON_TABLE(ea.rule_value ,"$[*]" COLUMNS( item varchar(50) PATH "$" )) vt	on vt.item!=''
		where
            ea.is_test is ?
			and ea.project_id = ?
			and ea.user_type = ?
			and ea.rule_form_id = ?
			and ea.rule_name='您认为本单位选人用人工作存在的问题是什么'
			and vt.item is not null
		group by 
		    vt.item
) vit 
 group by rule_name;
        `
        try {
            const result = await sequelize.query(sql, {
            replacements: [test, project_id, USER_TYPES.DEMO_EVA, formId],
            type: QueryTypes.SELECT
        })
        const result2 = await sequelize.query(sql2, {
            replacements: [test, project_id, USER_TYPES.DEMO_EVA, formId],
            type: QueryTypes.SELECT
        })
            if (result && result.length > 0) {
                const project: any = await this.projectMapper.findOne({ pk: project_id });
                return {
                    title: `${project.object_group_name}-${project.ef_year}年${form.name}`,
                    name: form.name, data: result, data2: result2
                };

            }
        } catch (error) {
            logger.error(error);
        }
        return { data: [] };

    }
    async exportXlsxSheet(test: boolean, project_id: string, rowStart: number, index: number): Promise<SheetMeta> {
        const { title, name, data, data2 } = await this.getResultData(project_id, test);
        if (data.length === 0) {
            return { merges: [], data: [], name };
        }
        const result_data = [];
        result_data.push([`${index + 1}.${title}`, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""])
        result_data.push(["一、本单位选人用人工作民主评议结果", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""]);
        const headers = ["结果", "", "", "", "好", "", "", "一般", "", "", "不好", "", "", "不了解", "", "", "好和一般所占百分比", "", "", ""];
        result_data.push(headers);
        for (const d of data) {
            result_data.push([d.rule_name, "", "", "", d?.a, "", "", d?.b, "", "", d?.c, "", "", d?.d, "", "", d?.posiv_pert, "", "", ""]);
        }
        const row2 = data2[0];
        result_data.push(["二、您认为本单位选人用人工作存在的主要问题是什么?（可多选）", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""])
        result_data.push(["①落实党中央关于领导班子和干部队伍建设工作要求有差距", "", "", "", "②选人用人把关不严、质量不高", "", "", "", "③坚持事业为上不够，不能做到以事择人、人岗相适", "", "", "", "④激励担当作为用人导向不鲜明、论资排辈情况比较严重", "", "", "", "⑤选人用人“个人说了算”", "", "", ""]);

        result_data.push([row2?.a, "", "", "", row2?.b, "", "", "", row2?.c, "", "", "", row2?.d, "", "", "", row2?.e, "", "", ""])
        result_data.push(["⑥任人唯亲、拉帮结派", "", "", "", "⑦跑官要官、买官卖官、说情打招呼", "", "", "", "⑧执行干部选拔任用政策规定不严格", "", "", "", "⑨干部队伍建设统筹谋划不够，结构不合理", "", "", "", "⑩干部队伍能力素质不适应工作要求", "", "", ""]);
        result_data.push([row2?.f, "", "", "", row2?.g, "", "", "", row2?.h, "", "", "", row2?.i, "", "", "", row2?.j, "", "", ""])

        const merges = [
            { s: { c: 0, r: rowStart }, e: { c: 19, r: rowStart } },

            { s: { c: 0, r: rowStart + 1 }, e: { c: 19, r: rowStart + 1 } },

            { s: { c: 0, r: rowStart + 2 }, e: { c: 3, r: rowStart + 2 } },
            { s: { c: 4, r: rowStart + 2 }, e: { c: 6, r: rowStart + 2 } },
            { s: { c: 7, r: rowStart + 2 }, e: { c: 9, r: rowStart + 2 } },
            { s: { c: 10, r: rowStart + 2 }, e: { c: 12, r: rowStart + 2 } },
            { s: { c: 13, r: rowStart + 2 }, e: { c: 15, r: rowStart + 2 } },
            { s: { c: 16, r: rowStart + 2 }, e: { c: 19, r: rowStart + 2 } },

            { s: { c: 0, r: rowStart + 3 }, e: { c: 3, r: rowStart + 3 } },
            { s: { c: 4, r: rowStart + 3 }, e: { c: 6, r: rowStart + 3 } },
            { s: { c: 7, r: rowStart + 3 }, e: { c: 9, r: rowStart + 3 } },
            { s: { c: 10, r: rowStart + 3 }, e: { c: 12, r: rowStart + 3 } },
            { s: { c: 13, r: rowStart + 3 }, e: { c: 15, r: rowStart + 3 } },
            { s: { c: 16, r: rowStart + 3 }, e: { c: 19, r: rowStart + 3 } },

            { s: { c: 0, r: rowStart + 4 }, e: { c: 3, r: rowStart + 4 } },
            { s: { c: 4, r: rowStart + 4 }, e: { c: 6, r: rowStart + 4 } },
            { s: { c: 7, r: rowStart + 4 }, e: { c: 9, r: rowStart + 4 } },
            { s: { c: 10, r: rowStart + 4 }, e: { c: 12, r: rowStart + 4 } },
            { s: { c: 13, r: rowStart + 4 }, e: { c: 15, r: rowStart + 4 } },
            { s: { c: 16, r: rowStart + 4 }, e: { c: 19, r: rowStart + 4 } },

            { s: { c: 0, r: rowStart + 5 }, e: { c: 19, r: rowStart + 5 } },

            { s: { c: 0, r: rowStart + 6 }, e: { c: 3, r: rowStart + 6 } },
            { s: { c: 4, r: rowStart + 6 }, e: { c: 7, r: rowStart + 6 } },
            { s: { c: 8, r: rowStart + 6 }, e: { c: 11, r: rowStart + 6 } },
            { s: { c: 12, r: rowStart + 6 }, e: { c: 15, r: rowStart + 6 } },
            { s: { c: 16, r: rowStart + 6 }, e: { c: 19, r: rowStart + 6 } },

            { s: { c: 0, r: rowStart + 7 }, e: { c: 3, r: rowStart + 7 } },
            { s: { c: 4, r: rowStart + 7 }, e: { c: 7, r: rowStart + 7 } },
            { s: { c: 8, r: rowStart + 7 }, e: { c: 11, r: rowStart + 7 } },
            { s: { c: 12, r: rowStart + 7 }, e: { c: 15, r: rowStart + 7 } },
            { s: { c: 16, r: rowStart + 7 }, e: { c: 19, r: rowStart + 7 } },

            { s: { c: 0, r: rowStart + 8 }, e: { c: 3, r: rowStart + 8 } },
            { s: { c: 4, r: rowStart + 8 }, e: { c: 7, r: rowStart + 8 } },
            { s: { c: 8, r: rowStart + 8 }, e: { c: 11, r: rowStart + 8 } },
            { s: { c: 12, r: rowStart + 8 }, e: { c: 15, r: rowStart + 8 } },
            { s: { c: 16, r: rowStart + 8 }, e: { c: 19, r: rowStart + 8 } },

            { s: { c: 0, r: rowStart + 9 }, e: { c: 3, r: rowStart + 9 } },
            { s: { c: 4, r: rowStart + 9 }, e: { c: 7, r: rowStart + 9 } },
            { s: { c: 8, r: rowStart + 9 }, e: { c: 11, r: rowStart + 9 } },
            { s: { c: 12, r: rowStart + 9 }, e: { c: 15, r: rowStart + 9 } },
            { s: { c: 16, r: rowStart + 9 }, e: { c: 19, r: rowStart + 9 } },
        ];
        return { merges, data: result_data, name };
    }
}
export { ResultDemoEvaMapper }